import yaml
import openpyxl
import logging
import sys
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

class Utility:

    @staticmethod
    def send_email(receivers, subject, path, content):
        """
        发送电子邮件
        :param receivers: 收件人
        :param subject: 主题
        :param path: 附件
        :param content: 正文
        :return:
        """
        """邮箱基本设置"""
        # 邮件服务器地址
        server = 'smtp.163.com'
        # 邮件服务器端口
        port = 25
        # 发件人地址
        sender = '18292623930@163.com'
        # 授权码
        code = 'AVNCNFWSUJQEALFA'

        """写邮件"""
        # 创建邮件对象
        mail = MIMEMultipart()
        # 发件人
        mail['from'] = sender
        # 收件人
        mail['to'] = receivers
        # 主题
        mail['subject'] = subject

        """附件"""
        # 读取附件
        with open(path, 'rb') as file:
            report = file.read()
        # 对附件进行编码
        attachment = MIMEText(report, 'base64', 'utf-8')
        # 设置附件类型
        attachment['Content-Type'] = 'application/octet-stream'
        # 设置附件的处理方式
        attachment['Content-Disposition'] = 'attachment;filename=%s' % path.split('/')[-1]
        # 添加附件
        mail.attach(attachment)

        """正文"""
        # 对正文进行编码
        body = MIMEText(content, 'html', 'utf-8')
        # 添加正文
        mail.attach(body)

        """发送邮件"""
        # 创建SMTP服务
        smtp = smtplib.SMTP()
        # 连接邮件服务器
        smtp.connect(server, port)
        # 登陆邮件服务器
        smtp.login(sender, code)
        # 发送邮件
        smtp.sendmail(sender, receivers.split(';'), mail.as_string())
        # 关闭smtp服务
        smtp.close()
        print('邮件发送结束！')

    @staticmethod
    def get_logger(path, level=logging.DEBUG):
        # 获取日志对象
        logger = logging.getLogger()
        # 设置日志级别
        logger.setLevel(level)
        # 设置日志的格式
        formatter = logging.Formatter('[%(asctime)s]-[%(filename)s]-[%(levelname)s]:[%(message)s]')

        """输出到控制台"""
        # 创建控制台处理器
        sh = logging.StreamHandler(sys.stdout)
        # 设置日志格式
        sh.setFormatter(formatter)
        # 将控制台处理器添加到日志对象中
        logger.addHandler(sh)

        # 输出到文件
        fh = logging.FileHandler(path, 'a', encoding='utf-8')
        # 设置日志格式
        fh.setFormatter(formatter)
        # 将控制台处理器添加到日志对象中
        logger.addHandler(fh)

        return logger

    @staticmethod
    def get_csv(path):
        """
        读取csv格式的数据文件
        :param path: 文件路径
        :return: 返回一个元组，例如：(('admin', '123456'),('user0','123456'))
        """
        with open(path, 'r', encoding='utf-8') as file:
            # 读取所有的行
            lines = file.readlines()
            # 转换为指定的格式
            lines = tuple(tuple(s.strip() for s in line.split(',')) for line in lines)
            return lines[1:]

    @staticmethod
    def get_yaml(path):
        """
        读取yaml格式的数据文件
        :param path: 文件路径
        :return: dict
        """
        # 读取文件
        with open(path, 'r', encoding='utf-8') as file:
            content = file.read()
            # 解析yaml文件
            return yaml.safe_load(content)

    @staticmethod
    def get_excel(bookname, sheetname):
        """
        读取Excel工作表
        :param bookname: 工作簿名称
        :param sheetname: 工作表名称
        :return: 返回一个元组，例如：(('admin', '123456'),('user0','123456'))
        """
        # 加载工作簿
        workbook = openpyxl.load_workbook(bookname)
        # 获取工作表
        worksheet = workbook[sheetname]
        # 获取单元格
        # d = []
        # for r in worksheet:
        #     t = []
        #     for cell in r:
        #         t.append(cell.value)
        #     d.append(tuple(t))
        # print(tuple(d))

        # print(tuple(worksheet.values))
        # 返回数据
        return tuple([tuple(cell.value for cell in r) for r in worksheet])[1:]

    @staticmethod
    def get_excel_as_dict(bookname,sheetname):
        """
        读取Excel工作表
        :param bookname: 工作簿名称
        :param sheetname: 工作表名称
        :return: 返回一个元组，例如： ({'no':1, 'step':'打开网页', 'command':'open', 'by':None, 'value':None, 'text':'http://localhost/ranzhi/www')} )
        """
        # 加载工作簿
        workbook = openpyxl.load_workbook(bookname)
        # 获取工作表
        worksheet = workbook[sheetname]
        # 获取所有数据
        lines = tuple(worksheet.values)
        # 获取所有的key
        keys = lines[0]
        d = []
        for line in lines[1:]:
            t = {}
            # 将数据保存到字典中
            for key, value in zip(keys, line):
                t[key] = value
            d.append(t)

        return tuple(d)